package com.hua.hiring.talent.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.hua.hiring.common.PageModel;
import com.hua.hiring.common.util.DBUtil;
import com.hua.hiring.common.util.Util;
import com.hua.hiring.talent.entity.ApplicationEntity;

public class ApplicationDao {
	
	private static final Logger LOG = Logger.getLogger(ApplicationDao.class); 
	/**
	 * 简历的查询(根据分页)
	 *
	 * @param: @return
	 * @return: List<ResumeEntity>
	 * @author: 刘天印
	 * @date: 2018年11月18日 下午3:53:31
	 * @throws RuntimeException
	 */
	public PageModel<ApplicationEntity> listByTelantPage(int talentId,int pageNo,int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("p.name AS 'position$name', ");
		sb.append("c.name AS 'company$name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		sb.append("ORDER BY id DESC ");
		sb.append("LIMIT ?, ? ");
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, talentId);
			//从哪条记录开始
			preparedStatement.setInt(2, (pageNo-1)*pageSize);
			//每页显示多少条信息
			preparedStatement.setInt(3, pageSize);
			resultSet = preparedStatement.executeQuery();
			//每批查询出来的数据
			List<ApplicationEntity> applicationList = new ArrayList<>();
			while (resultSet.next()) {
				//第一批数据
				ApplicationEntity applicationEntity = new ApplicationEntity();
				applicationEntity.setResume$intention(resultSet.getString("resume$intention"));
				applicationEntity.setCompany$name(resultSet.getString("position$name"));
				applicationEntity.setCompany$name(resultSet.getString("company$name"));
				applicationEntity.setId(resultSet.getInt("id"));
				applicationEntity.setAppTime(resultSet.getTimestamp("app_time"));
				applicationEntity.setStatus(resultSet.getInt("status"));
				applicationEntity.setHandleTime(resultSet.getTimestamp("handle_time"));
				//把数据存放在List里
				applicationList.add(applicationEntity);
			}
			//构建分页实体
			return new PageModel<ApplicationEntity>(applicationList,pageNo,pageSize,getAllTelantRecords(talentId));
		} catch (SQLException e) {
			Util.e(LOG, "该用户在查询分页求职管理中查询失败", e);
			return null ;
		}finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		} 
		
	}
	/**
	 *求职者取得共有多少条记录
	 *
	 * @param: @param talentId
	 * @param: @param pageNo
	 * @param: @param pageSize
	 * @param: @return
	 * @return: Integer
	 * @author: 刘天印
	 * @date: 2018年11月29日 下午3:16:52
	 */
	public static Integer getAllTelantRecords(int talentId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT count(*)");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, talentId);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException e) {
			Util.e(LOG, "该用户在查询分页求职信息总记录数时失败", e);
			return null ;
		}finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		} 
	}
	/**
	 * 企业：求职信息的查询(根据分页)来审核
	 *
	 * @param: @return
	 * @return: List<ResumeEntity>
	 * @author: 刘天印
	 * @date: 2018年11月18日 下午3:53:31
	 * @throws RuntimeException
	 */
	public PageModel<ApplicationEntity> listByCompanyVerifyPage(ApplicationEntity applicationEntity,int pageNo,int pageSize) {
		
		StringBuilder sb = new StringBuilder();
		List<Object> list = new ArrayList<>();
		sb.append("SELECT ");
		sb.append("p.name AS 'position$name', ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("t.name AS 'talent$name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = ? ");
		Integer companyId = applicationEntity.getCompany$Id();
		list.add(companyId);
		//职位模糊查询
		String positionName = applicationEntity.getPosition$name();
		if(StringUtils.isNoneBlank(positionName)) {
			sb.append("AND p.name LIKE ? ");
			list.add("%" + applicationEntity.getPosition$name() + "%");
		}
		//审核状态查询
		Integer status = applicationEntity.getStatus();
		if(status != null) {
			sb.append("AND a.status = ? ");
			list.add(applicationEntity.getStatus());
		}
		sb.append("ORDER BY id DESC ");
		sb.append("LIMIT ?, ? ");
		list.add((pageNo-1)*pageSize);
		list.add(pageSize);
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			
			DBUtil.setParameter(list, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			//每批查询出来的数据
			List<ApplicationEntity> applicationList = new ArrayList<>();
			while (resultSet.next()) {
				//第一批数据
				ApplicationEntity application = new ApplicationEntity();
				application.setPosition$name(resultSet.getString("position$name"));
				application.setResume$intention(resultSet.getString("resume$intention"));
				application.setTalent$name(resultSet.getString("talent$name"));
				application.setId(resultSet.getInt("id"));
				application.setAppTime(resultSet.getTimestamp("app_time"));
				application.setStatus(resultSet.getInt("status"));
				application.setHandleTime(resultSet.getTimestamp("handle_time"));
				//把数据存放在List里
				applicationList.add(application);
			}
			//构建分页实体
			return new PageModel<ApplicationEntity>(applicationList,pageNo,pageSize,getAllCompanyVerifyRecords(applicationEntity));
		} catch (SQLException e) {
			Util.e(LOG, "该企业在查询求职信息审核分页中查询失败", e);
			return null ;
		}finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		} 
		
	}
	/**
	 *求职者取得共有多少条记录
	 *
	 * @param: @param talentId
	 * @param: @param pageNo
	 * @param: @param pageSize
	 * @param: @return
	 * @return: Integer
	 * @author: 刘天印
	 * @date: 2018年11月29日 下午3:16:52
	 */
	public static Integer getAllCompanyVerifyRecords(ApplicationEntity applicationEntity) {
		StringBuilder sb = new StringBuilder();
		List<Object> list = new ArrayList<>();
		sb.append("SELECT count(*)");
		sb.append("FROM application a, resume r, position p, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = ? ");
		Integer companyId = applicationEntity.getCompany$Id();
		list.add(companyId);
		//职位模糊查询
		String positionName = applicationEntity.getPosition$name();
		if(StringUtils.isNoneBlank(positionName)) {
			sb.append("AND p.name LIKE ? ");
			list.add("%" + applicationEntity.getPosition$name() + "%");
		}
		//审核状态查询
		Integer status = applicationEntity.getStatus();
		if(status != null) {
			sb.append("AND a.status = ? ");
			list.add(applicationEntity.getStatus());
		}
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			DBUtil.setParameter(list, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException e) {
			Util.e(LOG, "该企业在查询求职信息审核分页中的总记录查询失败", e);
			return null ;
		}finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		} 
	}
	/**
	 * 简历的查询
	 *
	 * @param: @return
	 * @return: List<ResumeEntity>
	 * @author: 刘天印
	 * @date: 2018年11月18日 下午3:53:31
	 * @throws RuntimeException
	 * @Deprecated
	 */
	@Deprecated
	public List<ApplicationEntity> list(int id) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("p.name AS 'position$name', ");
		sb.append("c.name AS 'company$name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		sb.append(" ORDER BY id DESC ");
		List<ApplicationEntity> applicationlist= DBUtil.executeQuery(ApplicationEntity.class, sb.toString(), id);
		return applicationlist ;
	}
	/**
	 * 根据前台传来的id删除求职信息
	 *
	 * @param: @param id
	 * @param: @return
	 * @return: int
	 * @author: 刘天印
	 * @date: 2018年11月24日 下午10:20:37
	 * @throws RuntimeException
	 */
	public int delete(int id) {
		String sql = "DELETE FROM application WHERE id=?" ;
		return DBUtil.executeUpdate(sql, id);
				
	}
	/**
	 * 根据前台传来的id投简历
	 *
	 * @param: @param positionId
	 * @param: @param resumeId
	 * @param: @param talentId
	 * @param: @return
	 * @return: ApplicationEntity
	 * @author: 刘天印
	 * @date: 2018年11月25日 下午4:24:00
	 * @throws RuntimeException
	 */
	public int apply(int positionId, int resumeId, int talentId) {
		String sql="INSERT INTO application VALUES(NULL, ?, ?, ?, 1, NOW(), NULL);";
		return DBUtil.executeUpdate(sql, positionId,talentId,resumeId);
	}
	/**
	 * 判断该用户是否投递了该简历
	 *
	 * @param: @param talentId
	 * @param: @param id
	 * @param: @return
	 * @return: ApplicationEntity
	 * @author: 刘天印
	 * @date: 2018年11月25日 下午5:23:04
	 */
	public ApplicationEntity isApplied(int talentId, int id) {
		String sql= "SELECT status FROM application WHERE talent_id=? AND position_id=?";
		return DBUtil.getUniqueResult(ApplicationEntity.class, sql, talentId,id);
	}
	/**
	 * 企业进行简历审核
	 *
	 * @param: @param applicationId
	 * @param: @param status
	 * @param: @return
	 * @return: int
	 * @author: 刘天印
	 * @date: 2018年11月30日 下午5:32:39
	 */
	public int verify(int applicationId, int status) {
		String sql = "UPDATE application SET status =?, handle_time=NOW() WHERE id = ?";
		return DBUtil.executeUpdate(sql, status,applicationId);
	}

}
